﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateOverflowService]
	@OverflowServiceName sysname = NULL OUTPUT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @Id char(32)
	
	SET @Id = REPLACE(NEWID(), '-', '')
	
	DECLARE @ServiceName sysname
	DECLARE @QueueName sysname
	DECLARE @OverflowActivationSP sysname
	
	SET @ServiceName = ISNULL(@OverflowServiceName, 'O_' + @Id)
	SET @QueueName = 'OQ_' + @Id
	SET @OverflowActivationSP = 'OverflowAct_' + @Id
	
	DECLARE @sql nvarchar(max)
	
	SET @sql = N'CREATE QUEUE '+ @QueueName 
	
	EXEC sp_executesql @sql
	
	SET @sql =
		N'CREATE PROCEDURE ' + @OverflowActivationSP + N'
		  AS
		  BEGIN
			DECLARE @messages TABLE(ConversationHandle uniqueidentifier, RecvMessage xml, RecvMessageName sysname)
	
			WHILE (1=1)
			BEGIN

			BEGIN TRANSACTION;

			WAITFOR
			( RECEIVE conversation_handle
					 ,message_body
					 ,message_type_name
			  FROM [' + @QueueName + N']
			  INTO @messages
			), TIMEOUT 5000;

			IF (@@ROWCOUNT = 0)
			BEGIN
			  ROLLBACK TRANSACTION;
			  BREAK;
			END

			DECLARE @RecvMessage xml
			DECLARE @RecvMessageName sysname
			DECLARE @DialogHandle uniqueidentifier
			DECLARE @RecvReqDlgHandle uniqueidentifier
			DECLARE @SubscriptionId bigint
			DECLARE @IsOverflowService bit
			
			DECLARE msgCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
			FOR 
				SELECT [ConversationHandle], [RecvMessage], [RecvMessageName], [DialogHandle], [SubscriptionId], CONVERT(bit, CASE WHEN LEFT([Target], 2) = ''O_'' THEN 1 ELSE 0 END) AS IsOverflowService
				FROM @messages
				JOIN [ServiceMetadata] ON [Source] = ''' + @ServiceName + N'''
				LEFT JOIN [Subscription] ON [EventName] = [RecvMessage].value(''declare namespace x="urn:www-vecozo-com:types:events:v2"; (/x:GenericEvent/x:Name)[1]'', ''nvarchar(128)'') AND [ServiceName] = [Target]
				
			OPEN msgCursor
			
			FETCH NEXT FROM msgCursor INTO 
				@RecvReqDlgHandle, @RecvMessage, @RecvMessageName, @DialogHandle, @SubscriptionId, @IsOverflowService
			
			WHILE @@FETCH_STATUS = 0
			BEGIN

			
				IF @RecvMessageName =
					N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog''
				BEGIN
				   END CONVERSATION @RecvReqDlgHandle;
				END
				ELSE IF @RecvMessageName =
					N''http://schemas.microsoft.com/SQL/ServiceBroker/Error''
				BEGIN
				   END CONVERSATION @RecvReqDlgHandle;
				END
				ELSE IF (@SubscriptionId IS NOT NULL OR @IsOverflowService = 1)
				BEGIN
					SEND ON CONVERSATION @DialogHandle (@RecvMessage)
				END
				
				FETCH NEXT FROM msgCursor INTO 
					@RecvReqDlgHandle, @RecvMessage, @RecvMessageName, @DialogHandle, @SubscriptionId, @IsOverflowService
			END
			
			CLOSE msgCursor
			
			DELETE @messages;

			COMMIT TRANSACTION;

			END

		  END'
	
	EXEC sp_executesql @sql
	
	SET @sql = N'ALTER QUEUE '+ @QueueName + N' WITH STATUS = ON, RETENTION = OFF ,
						   ACTIVATION (
								STATUS = OFF,
								PROCEDURE_NAME = ' + @OverflowActivationSP + N',
								MAX_QUEUE_READERS = 1, 
								EXECUTE AS SELF)'
   
   EXEC sp_executesql @sql
   
   SET @sql = N'CREATE SERVICE ' + @ServiceName + N' ON QUEUE ' + @QueueName + ' ([DEFAULT])'

   EXEC sp_executesql @sql	
   
   SET @OverflowServiceName = @ServiceName			
END
